DROP PROCEDURE [dbo].[pr0061]
GO
--- Purpose: cap nhat lai so du cho cac kho hang 

--- Purpose: Thay doi cach tinh gia tri ton cuoi
CREATE  PROCEDURE [dbo].[pr0061] 	@KindVoucherID as tinyint, @DivisionID varchar(20), @TranMonth int, @TranYear int,
				@WareHouseID varchar(20), @WareHouseID2 varchar(20), @InventoryID as varchar(20),
				@DebitAccountID_Old varchar(20),  @CreditAccountID_Old varchar(20),  @DebitAccountID_New varchar(20), @CreditAccountID_New varchar(20), 
				@OldQuantity Money, @NewQuantity Money, @OldConvertedAmount Money, @NewConvertedAmount Money
AS

----------------------============================================----------------------------------------------------
Print ' Kind: '+str(@KindVoucherID)+' New :' +@CreditAccountID_Old+'  ' +@CreditAccountID_New
If @KindVoucherID in (1,5,7,9) --- truong hop nhap kho
	 Begin
		If @DebitAccountID_Old = @DebitAccountID_New
		  	Begin	
		   		Update tb0106		
				Set 	DebitQuantity 	=	isnull(DebitQuantity,0)	- isnull(@OldQuantity,0) +  isnull(@NewQuantity,0),
					DebitAmount 	=	isnull(DebitAmount,0)	- Isnull(@OldConvertedAmount,0) + Isnull(@NewConvertedAmount,0) 
					--EndQuantity		=	isnull(BeginQuantity,0) + isnull(DebitQuantity,0) - isnull(CreditQuantity,0),  --EndQuantity		=	isnull(EndQuantity,0)		- isnull(@OldQuantity,0) +  isnull(@NewQuantity,0),
					--EndAmount		=	isnull(BeginAmount,0) + isnull(DebitAmount,0) - isnull(CreditAmount,0) --EndAmount		=	isnull(EndAmount,0)		- Isnull(@OldConvertedAmount,0) + Isnull(@NewConvertedAmount,0) 
		 		Where 	InventoryID =@InventoryID and
					DivisionID =@DivisionID and
					WareHouseID =@WareHouseID and
					InventoryAccountID = @DebitAccountID_New  and
					TranMonth =@TranMonth and
					TranYear =@TranYear

				Update tb0106		
				Set 	EndQuantity		=	isnull(BeginQuantity,0) + isnull(DebitQuantity,0) - isnull(CreditQuantity,0),
					EndAmount		=	isnull(BeginAmount,0) + isnull(DebitAmount,0) - isnull(CreditAmount,0) 
		 		Where 	InventoryID =@InventoryID and
					DivisionID =@DivisionID and
					WareHouseID =@WareHouseID and
					InventoryAccountID = @DebitAccountID_New  and
					TranMonth =@TranMonth and
					TranYear =@TranYear
                           	End
		Else
		   Begin
			
			Exec pr0054 @DivisionID, @WareHouseID, @TranMonth, @TranYear, @InventoryID, @OldConvertedAmount, @OldQuantity, @DebitAccountID_Old , @CreditAccountID_Old, 1 -- (1) la cap nhat tang,(0 ) la cap nhat giam
			Exec pr0053 @DivisionID, @WareHouseID, @TranMonth, @TranYear, @InventoryID, @NewConvertedAmount, @NewQuantity, @DebitAccountID_New , @CreditAccountID_New, 1 -- (1) la cap nhat tang,(0 ) la cap nhat giam
		   End
 	End --- Of Nhap kho
----------------------============================================----------------------------------------------------
	If @KindVoucherID in (2,4,6,8) --- truong hop Xuat kho
		Begin
		   	 If @CreditAccountID_Old = @CreditAccountID_New
				Begin
					Update tb0106			 
						Set 	CreditQuantity 	=		isnull(CreditQuantity,0)	- isnull(@OldQuantity,0) +  isnull(@NewQuantity,0),
							CreditAmount 	=		isnull(CreditAmount,0)	- Isnull(@OldConvertedAmount,0) + Isnull(@NewConvertedAmount,0) 
							--EndQuantity		=	isnull(BeginQuantity,0) + isnull(DebitQuantity,0) - isnull(CreditQuantity,0),  --isnull(EndQuantity,0)	+ isnull(@OldQuantity,0) -  isnull(@NewQuantity,0),
							--EndAmount		=	isnull(BeginAmount,0) + isnull(DebitAmount,0) - isnull(CreditAmount,0) --isnull(EndAmount,0)		+ Isnull(@OldConvertedAmount,0) - Isnull(@NewConvertedAmount,0) 
					 Where 		InventoryID =@InventoryID and
							DivisionID =@DivisionID and
							WareHouseID =@WareHouseID and
							InventoryAccountID = @CreditAccountID_New and
							TranMonth =@TranMonth and
							TranYear =@TranYear
					Update tb0106		
						Set 	EndQuantity		=	isnull(BeginQuantity,0) + isnull(DebitQuantity,0) - isnull(CreditQuantity,0),
							EndAmount		=	isnull(BeginAmount,0) + isnull(DebitAmount,0) - isnull(CreditAmount,0) 
				 		Where 	InventoryID =@InventoryID and
							DivisionID =@DivisionID and
							WareHouseID =@WareHouseID and
							InventoryAccountID = @CreditAccountID_New  and
							TranMonth =@TranMonth and
							TranYear =@TranYear
				End
			Else
				Begin
					Exec pr0054 @DivisionID, @WareHouseID, @TranMonth, @TranYear, @InventoryID, @OldConvertedAmount, @OldQuantity, @DebitAccountID_Old , @CreditAccountID_Old, 0 -- (1) la cap nhat tang,(0 ) la cap nhat giam
					Exec pr0053 @DivisionID, @WareHouseID, @TranMonth, @TranYear, @InventoryID, @NewConvertedAmount, @NewQuantity, @DebitAccountID_New , @CreditAccountID_New, 0 -- (1) la cap nhat tang,(0 ) la cap nhat giam
				End

		  End --- Of Xuat kho
----------------------============================================----------------------------------------------------
		if @KindVoucherID =3  --- Nhap VCNB
			Begin
				---- Kho nhap
			      IF 	@DebitAccountID_Old = @DebitAccountID_New
				Begin
					Update tb0106		
					Set 	DebitQuantity 	=	isnull(DebitQuantity,0)	- isnull(@OldQuantity,0) +  isnull(@NewQuantity,0),
						DebitAmount 	=	isnull(DebitAmount,0)	- Isnull(@OldConvertedAmount,0) + Isnull(@NewConvertedAmount,0) ,
						InDebitQuantity  = 	isnull(InDebitQuantity,0)	- isnull(@OldQuantity,0) +  isnull(@NewQuantity,0),
						InDebitAmount 	=	isnull(InDebitAmount,0)	- Isnull(@OldConvertedAmount,0) + Isnull(@NewConvertedAmount,0) 
						--EndQuantity		=	isnull(BeginQuantity,0) + isnull(DebitQuantity,0) - isnull(CreditQuantity,0), -- EndQuantity		=	isnull(EndQuantity,0)		- isnull(@OldQuantity,0) +  isnull(@NewQuantity,0),
						--EndAmount		=	isnull(BeginAmount,0) + isnull(DebitAmount,0) - isnull(CreditAmount,0) --EndAmount		=	isnull(EndAmount,0)		- Isnull(@OldConvertedAmount,0) + Isnull(@NewConvertedAmount,0) 
					 Where 		InventoryID =@InventoryID and
							DivisionID =@DivisionID and
							WareHouseID =@WareHouseID and
							InventoryAccountID = @DebitAccountID_New and
							TranMonth =@TranMonth and
							TranYear =@TranYear
					Update tb0106		
					Set 	EndQuantity		=	isnull(BeginQuantity,0) + isnull(DebitQuantity,0) - isnull(CreditQuantity,0),
						EndAmount		=	isnull(BeginAmount,0) + isnull(DebitAmount,0) - isnull(CreditAmount,0) 
			 		Where 	InventoryID =@InventoryID and
						DivisionID =@DivisionID and
						WareHouseID =@WareHouseID and
						InventoryAccountID = @DebitAccountID_New  and
						TranMonth =@TranMonth and
						TranYear =@TranYear
				End
			   ELSE	
				Begin
					Exec pr0054 @DivisionID, @WareHouseID, @TranMonth, @TranYear, @InventoryID, @OldConvertedAmount, @OldQuantity, @DebitAccountID_Old , @CreditAccountID_Old, 11 -- (1) la cap nhat tang,(0 ) la cap nhat giam; 11 la cho VCNB
					Exec pr0053 @DivisionID, @WareHouseID, @TranMonth, @TranYear, @InventoryID, @NewConvertedAmount, @NewQuantity, @DebitAccountID_New , @CreditAccountID_New, 11 -- (1) la cap nhat tang,(0 ) la cap nhat giam

				End
			
				---- Kho Xuat
				If @CreditAccountID_New = @CreditAccountID_Old
					Begin
						Update tb0106		
						Set 	CreditQuantity 	=	isnull(CreditQuantity,0)	- isnull(@OldQuantity,0) +  isnull(@NewQuantity,0),
							CreditAmount 	=	isnull(CreditAmount,0)	- Isnull(@OldConvertedAmount,0) + Isnull(@NewConvertedAmount,0) ,
							InCreditQuantity  = 	isnull(InCreditQuantity,0)	- isnull(@OldQuantity,0) +  isnull(@NewQuantity,0),
							InCreditAmount 	=	isnull(InCreditAmount,0)	- Isnull(@OldConvertedAmount,0) + Isnull(@NewConvertedAmount,0) 
							--EndQuantity		=	isnull(BeginQuantity,0) + isnull(DebitQuantity,0) - isnull(CreditQuantity,0), --EndQuantity		=	isnull(EndQuantity,0)		+ isnull(@OldQuantity,0) -  isnull(@NewQuantity,0),
							--EndAmount		=	isnull(BeginAmount,0) + isnull(DebitAmount,0) - isnull(CreditAmount,0) --EndAmount		=	isnull(EndAmount,0)		+ Isnull(@OldConvertedAmount,0) - Isnull(@NewConvertedAmount,0) 
						 Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID2 and
								InventoryAccountID =@CreditAccountID_New and
								TranMonth =@TranMonth and
								TranYear =@TranYear
						Update tb0106		
						Set 	EndQuantity		=	isnull(BeginQuantity,0) + isnull(DebitQuantity,0) - isnull(CreditQuantity,0),
							EndAmount		=	isnull(BeginAmount,0) + isnull(DebitAmount,0) - isnull(CreditAmount,0) 
				 		Where 	InventoryID =@InventoryID and
							DivisionID =@DivisionID and
							WareHouseID =@WareHouseID2 and
							InventoryAccountID = @CreditAccountID_New  and
							TranMonth =@TranMonth and
							TranYear =@TranYear
					End
				Else

				Begin
					Exec pr0054 @DivisionID, @WareHouseID2, @TranMonth, @TranYear, @InventoryID, @OldConvertedAmount, @OldQuantity, @DebitAccountID_Old , @CreditAccountID_Old, 10 -- (1) la cap nhat tang,(0 ) la cap nhat giam; 10 la cho VCNB
					Exec pr0053 @DivisionID, @WareHouseID2, @TranMonth, @TranYear, @InventoryID, @NewConvertedAmount, @NewQuantity, @DebitAccountID_New , @CreditAccountID_New, 10 -- (1) la cap nhat tang,(0 ) la cap nhat giam

				End
			End


GO